package com.eairlv.share.auto.service;

import cn.hutool.cache.CacheUtil;
import cn.hutool.cache.impl.FIFOCache;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSON;
import com.eairlv.share.auto.entity.Result;
import com.eairlv.share.auto.entity.bo.ShareSql;
import com.eairlv.share.auto.entity.bo.ShareTableResultForm;
import com.eairlv.share.auto.entity.bo.TemplateTableForm;
import com.eairlv.share.auto.entity.po.ShareTableDbPO;
import com.eairlv.share.auto.entity.po.ShareTablePO;
import com.eairlv.share.auto.entity.po.ShareTableRecordPO;
import com.eairlv.share.auto.entity.po.enums.ShareWay;
import com.eairlv.share.auto.entity.vo.ShareTableDbInfoVO;
import com.eairlv.share.auto.entity.vo.ShareTableInfoTableVO;
import com.eairlv.share.auto.entity.vo.ShareTableInfoVO;
import com.eairlv.share.auto.utils.BeanUtil;
import com.eairlv.share.auto.utils.LogUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author eairlv
 * @description
 * @date 10:10 2020/1/10
 */
@Slf4j
@Service
public class ShareService {

    /**
     * 数据库名缓存容量
     */
    public static final int DB_NAME_CACHE_CAPACITY = 20;

    /**
     * 数据库名缓存
     */
    private static FIFOCache<String, String> DB_NAME_CACHE = CacheUtil.newFIFOCache(DB_NAME_CACHE_CAPACITY);

    /**
     * 分片规则检查间隔, 10秒
     */
    public static final int SHARE_TABLE_CHECK_INTERVAL = 10000;

    /**
     * 数据库最大连接数，支持对分片数据的高并发请求
     */
    public static final int MAX_CONNECTION = 120;

    @Autowired
    ShareTableService shareTableService;

    @Autowired
    ShareTableDbService dbService;

    @Autowired
    ShareTableRecordService recordService;

    @Autowired
    DruidDataSource druidDataSource;

    /**
     * 分片规则检查
     */
    @Scheduled(fixedDelay = SHARE_TABLE_CHECK_INTERVAL)
    private void scheduled(){
        try {
            List<ShareTablePO> shareTablePOS = shareTableService.getShareByIdOrTemplateTableName(null, null);
            shareTablePOS.forEach(e -> {
                // 排除无需分片检查与分片检查周期不满足设置的检测周期间隔的分片规则
                if (e.getCheckInterval() != 0 && (e.getLastCheckTime() + e.getCheckInterval()) <= System.currentTimeMillis()){
                    checkShare(e, Boolean.TRUE);
                }
            });
        } catch (Exception e){
            log.error("share table scheduled error: {}", LogUtil.getExceptionMessage(e));
        }
    }

    /**
     * 执行分片
     * @param shareTablePO
     * @param auto
     */
    public void checkShare(ShareTablePO shareTablePO, Boolean auto) {
        checkShare(shareTablePO, null, auto);
    }

    private void checkShare(ShareTablePO shareTablePO, Date begin, Boolean auto) {
        try {
            // 获取DDL语句
            String ddl = getTemplateTableDDL(shareTablePO.getTemplateTableName());
            if (ddl == null){
                log.error("get template table {} ddl failed", shareTablePO.getTemplateTableName());
            } else {
                // 获取分片规则数据库
                List<DruidDataSource> druidDataSources = dbService.getShareDbByShareTableId(shareTablePO.getId())
                        .parallelStream()
                        .map(this::getDruidDataSource)
                        .collect(Collectors.toList());
                DateTime today = DateUtil.beginOfDay(DateUtil.date());
                // 遍历分片规则数据库
                druidDataSources.parallelStream().forEach(dataSource -> {
                    DruidPooledConnection connection = null;
                    try {
                        connection = dataSource.getConnection();
                        log.info("{}", connection.getMetaData().getURL());
                        if (begin == null){
                            // 自动执行，将今天设置为起始，shareTablePO.shareTableAdvance为应分表数
                            createTableByShareRule(shareTablePO, ddl, dataSource, today, auto);
                            ShareTablePO tempShareTablePO = BeanUtil.copyProperties(shareTablePO, ShareTablePO.class);
                            tempShareTablePO.setShareTableBegin(today.getTime());
                            showAllTableResult(tempShareTablePO, ddl, dataSource, today);
                        } else {
                            // 手动执行，使用配置的shareTablePO.shareTableBegin为起始，并且调用advanceShareTable()生成新的应分表数
                            createTableByShareRule(advanceShareTable(shareTablePO, begin, today), ddl, dataSource, begin, auto);
                            showAllTableResult(shareTablePO, ddl, dataSource, today);
                        }
                    } catch (SQLException e) {
                        log.error("checkShare error: {}", LogUtil.getExceptionMessage(e));
                    } finally {
                        closeConnection(connection);
                        dataSource.close();
                    }
                });
            }
            // 更新上次检测时间
            shareTablePO.setLastCheckTime(System.currentTimeMillis());
            shareTableService.update(BeanUtil.copyProperties(shareTablePO, ShareTablePO.class));
        } catch (Exception e){
            log.error("share table error: {}", LogUtil.getExceptionMessage(e));
        }
    }

    /**
     * 获取模板表的建表DDL语句
     * @param templateTableName
     * @return
     */
    private String getTemplateTableDDL(String templateTableName) {
        String ddl = null;
        DruidPooledConnection connection = null;
        try {
            connection = druidDataSource.getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("show create table " + templateTableName + ";");
            while(resultSet.next()){
                ddl = resultSet.getString("Create Table").replace("\n", " ");
                break;
            }
            resultSet.close();
            statement.close();
            return ddl;
        } catch (Exception e){
            log.warn("templateTableName:{} not exist", templateTableName);
        } finally {
            closeConnection(connection);
        }
        return ddl;
    }

    /**
     * 获取数据源
     * @param shareTableDbPO
     * @return
     */
    private DruidDataSource getDruidDataSource(ShareTableDbPO shareTableDbPO){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(shareTableDbPO.getUrl());
        druidDataSource.setUsername(shareTableDbPO.getUsername());
        druidDataSource.setPassword(shareTableDbPO.getPassword());
        druidDataSource.setDriverClassName(shareTableDbPO.getDriver());
        // 防止并发调用过高
        druidDataSource.setMaxActive(MAX_CONNECTION);
        return druidDataSource;
    }

    /**
     * 关闭连接
     * @param connection
     */
    private void closeConnection(DruidPooledConnection connection) {
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {}
        }
    }

    /**
     * 通过起止时间生成预设值的分片对象
     * @param shareTablePO
     * @param begin
     * @param end
     * @return
     */
    private ShareTablePO advanceShareTable(ShareTablePO shareTablePO, Date begin, Date end){
        ShareTablePO tempShareTablePO = BeanUtil.copyProperties(shareTablePO, ShareTablePO.class);
        Integer shareTableAdvance = tempShareTablePO.getShareTableAdvance();
        if (shareTablePO.getShareTableWay().equals(ShareWay.YEAR)){
            shareTableAdvance += Math.toIntExact(DateUtil.betweenYear(begin, end, Boolean.TRUE));
        } else if (shareTablePO.getShareTableWay().equals(ShareWay.MONTH)){
            shareTableAdvance += Math.toIntExact(DateUtil.betweenMonth(begin, end, Boolean.TRUE));
        } else if (shareTablePO.getShareTableWay().equals(ShareWay.DAY)){
            shareTableAdvance += Math.toIntExact(DateUtil.betweenDay(begin, end, Boolean.TRUE));
        }
        tempShareTablePO.setShareTableAdvance(shareTableAdvance);
        return tempShareTablePO;
    }


    /**
     * 通过分片规则创建表
     * @param shareTablePO
     * @param ddl
     * @param dataSource
     * @param begin
     * @param auto
     */
    private void createTableByShareRule(ShareTablePO shareTablePO, String ddl, DruidDataSource dataSource, Date begin, Boolean auto) {
        // 已存在的表
        List<String> excludeTables = findAlreadyExistsTable(shareTablePO.getTemplateTableName(), dataSource);
        // 生成建表语句
        List<ShareSql> shareSqls = ShareRule.shareRule(ddl, shareTablePO, begin, excludeTables);
        // 执行建表语句
        createShareTable(shareSqls.parallelStream().map(ShareSql::getSql).collect(Collectors.toList()), dataSource);
        // 自动建表结果报告
        createTableResult(shareTablePO, shareSqls, dataSource, auto);
    }

    /**
     * 通过模板表找到已存在的分片表
     * @param templateTableName
     * @param dataSource
     * @return
     */
    private List<String> findAlreadyExistsTable(String templateTableName, DruidDataSource dataSource) {
        List<String> existsTables = new ArrayList<>();
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(
                    "select table_name from information_schema.tables where table_schema = '" +
                            getDbNameByUrl(dataSource) + "' and table_name like '" +
                            templateTableName + "_%' order by table_name;");
            while(resultSet.next()){
                existsTables.add(resultSet.getString("table_name"));
            }
            resultSet.close();
            statement.close();
        } catch (Exception e){
            log.error("findAlreadyExistsTable error: {}", LogUtil.getExceptionMessage(e));
        } finally {
            closeConnection(connection);
        }
        return existsTables;
    }

    /**
     * 执行数据库分片建表语句
     * @param createSqlList
     * @param dataSource
     * @return
     */
    private void createShareTable(List<String> createSqlList, DruidDataSource dataSource) {
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            Statement statement = connection.createStatement();
            for (String sql: createSqlList){
                statement.executeUpdate(sql);
            }
            statement.close();
        } catch (Exception e){
            log.error("createShareTable error: {}", LogUtil.getExceptionMessage(e));
        } finally {
            closeConnection(connection);
        }
    }

    /**
     * 生成建表结果
     * @param shareTablePO
     * @param shareSqls
     * @param dataSource
     * @param auto
     */
    private void createTableResult(ShareTablePO shareTablePO, List<ShareSql> shareSqls, DruidDataSource dataSource, Boolean auto){
        List<String> allTables = findAlreadyExistsTable(shareTablePO.getTemplateTableName(), dataSource);
        List<String> newTables = shareSqls.parallelStream().map(ShareSql::getTableName).collect(Collectors.toList());
        List<String> success = new ArrayList<>();
        List<String> failure = new ArrayList<>();
        newTables.forEach(e -> {
            if (allTables.contains(e)){
                log.debug("---------------> {}  success", e);
                success.add(e);
            } else {
                log.debug("---------------> {}  failure", e);
                failure.add(e);
            }
        });
        if (!newTables.isEmpty()){
            recordService.insert(ShareTableRecordPO.builder()
                    .auto(auto)
                    .createTime(System.currentTimeMillis())
                    .success(success)
                    .failure(failure)
                    .shareTableId(shareTablePO.getId())
                    .build());
        }
    }

    /**
     * 查看全表建表结果
     * @param originalShareTablePO
     * @param ddl
     * @param dataSource
     * @param end
     *
     * 1.读取originalShareTablePO.shareTableBegin到end的相差分片数，加上originalShareTablePO.shareTableAdvance的数量，即为查看的总数量
     * 2.allTables基于shareTables的最大值与最小值进行筛选，仅对比应分片表与在应分片表中的范围内的实际所有表
     *
     * @return
     */
    private ShareTableInfoVO showAllTableResult(ShareTablePO originalShareTablePO, String ddl, DruidDataSource dataSource, Date end){
        DateTime begin = DateUtil.date(originalShareTablePO.getShareTableBegin());
        ShareTablePO shareTablePO = advanceShareTable(originalShareTablePO, begin, end);

        List<String> shareTables = ShareRule.shareRule(ddl, shareTablePO, begin, new ArrayList<>()).parallelStream().map(ShareSql::getTableName).collect(Collectors.toList());

        List<Integer> tableDate = shareTables.stream().map(this::getShareTableNameSuffix).sorted().collect(Collectors.toList());
        List<String> allTables = new ArrayList<>();
        if (!tableDate.isEmpty()){
            allTables = findAlreadyExistsTable(shareTablePO.getTemplateTableName(), dataSource).stream().filter(e -> {
                Integer shareTableNameSuffix = getShareTableNameSuffix(e);
                return shareTableNameSuffix != -1 && shareTableNameSuffix >= tableDate.get(0) && shareTableNameSuffix <= tableDate.get(tableDate.size() - 1);
            }).collect(Collectors.toList());
        }
        List<String> finalAllTables = allTables;
        List<String> differenceTables = shareTables.parallelStream().filter(e -> !finalAllTables.contains(e)).collect(Collectors.toList());
        log.debug("---------------> allTables: {}", JSON.toJSONString(allTables));
        log.debug("---------------> shareTables: {}", JSON.toJSONString(shareTables));
        log.debug("---------------> differenceTables: {}", JSON.toJSONString(differenceTables));
        List<ShareTableInfoTableVO> shareTableInfoTableVOS = allTables.stream().map(e -> getTableInfo(e, dataSource)).collect(Collectors.toList());
        return ShareTableInfoVO.builder()
                .allTables(allTables)
                .shareTables(shareTables)
                .differenceTables(differenceTables)
                .shareTableInfoTableVOS(shareTableInfoTableVOS)
                .build();
    }

    /**
     * 截取分片表名时间后缀
     * @param tableName
     * @return
     */
    private Integer getShareTableNameSuffix(String tableName){
        Integer defaultNumber = -1;
        try {
            String[] split = tableName.split("_");
            defaultNumber = Integer.parseInt(split[split.length - 1]);
        } catch (Exception ignore){}
        return defaultNumber;
    }

    /**
     * 查询表信息
     * @param tableName
     * @param dataSource
     * @return
     */
    private ShareTableInfoTableVO getTableInfo(String tableName, DruidDataSource dataSource) {
        ShareTableInfoTableVO shareTableInfoTableVO = ShareTableInfoTableVO.builder().build();
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(
                    "select * from information_schema.tables where table_schema = '" +
                            getDbNameByUrl(dataSource) +
                            "' and table_name = '" +
                            tableName + "'");
            while(resultSet.next()){
                shareTableInfoTableVO.setTableName(tableName);
                shareTableInfoTableVO.setEngine(resultSet.getString("ENGINE"));
                shareTableInfoTableVO.setRows(resultSet.getInt("TABLE_ROWS"));
                shareTableInfoTableVO.setDataSize(resultSet.getLong("DATA_LENGTH"));
                shareTableInfoTableVO.setCreateTime(resultSet.getString("CREATE_TIME"));
                shareTableInfoTableVO.setModifyTime(resultSet.getString("UPDATE_TIME"));
                shareTableInfoTableVO.setCoding(resultSet.getString("TABLE_COLLATION"));
                break;
            }
            resultSet.close();
            statement.close();
        } catch (Exception e){
            log.error("findAlreadyExistsTable error: {}", LogUtil.getExceptionMessage(e));
        } finally {
            closeConnection(connection);
        }
        return shareTableInfoTableVO;
    }

    /**
     * 通过数据库url连接获取数据库名
     * @param dataSource
     * @return
     */
    private String getDbNameByUrl(DruidDataSource dataSource) {
        String dbName = null;
        DruidPooledConnection connection = null;
        try {
            connection = dataSource.getConnection();
            String url = connection.getMetaData().getURL();
            dbName = DB_NAME_CACHE.get(url);
            if (dbName == null){
                Matcher matcherWithIP = Pattern.compile("(/.+?)\\?").matcher(url);
                matcherWithIP.find();
                Matcher matcherWithPort = Pattern.compile("(:.+?)\\?").matcher(matcherWithIP.group(0));
                matcherWithPort.find();
                Matcher matcher = Pattern.compile("(/.+?)\\?").matcher(matcherWithPort.group(0));
                matcher.find();
                dbName = matcher.group(0).replace("?", "").replace("/", "");
            }
            DB_NAME_CACHE.put(url, dbName);
        } catch (Exception e){
            log.error("findAlreadyExistsTable error: {}", LogUtil.getExceptionMessage(e));
        } finally {
            closeConnection(connection);
        }
        return dbName;
    }

    /**
     * 创建模板表
     * @param templateTableForm
     * @return
     */
    public Result createTemplate(TemplateTableForm templateTableForm) {
        if (!templateTableForm.getDdlSql().contains(templateTableForm.getTableName())){
            return Result.failMessage("ddl not contains tableName");
        }
        DruidPooledConnection connection = null;
        try {
            connection = druidDataSource.getConnection();
            Statement statement = connection.createStatement();
            if (getTemplateTableDDL(templateTableForm.getTableName()) != null && templateTableForm.getCover()){
                statement.executeUpdate("drop table " + templateTableForm.getTableName() + ";");
            }
            statement.executeUpdate(templateTableForm.getDdlSql());
            statement.close();
            return Result.ok();
        } catch (Exception e){
            log.error("create templateTable error: {}", LogUtil.getExceptionMessage(e));
        } finally {
            closeConnection(connection);
        }
        return Result.fail();
    }

    /**
     * 获取模板表的建表语句
     * @param tableName
     * @return
     */
    public Result<String> templateTableDDL(String tableName) {
        return Result.ok(getTemplateTableDDL(tableName));
    }

    /**
     * 执行分片建表
     * @param id
     * @return
     */
    public Result execute(Integer id) {
        ShareTablePO shareTablePO = shareTableService.getShareById(id);
        if (shareTablePO != null){
            checkShare(shareTablePO, DateUtil.beginOfDay(DateUtil.date(shareTablePO.getShareTableBegin())), Boolean.FALSE);
            return Result.ok();
        } else {
            return Result.failMessage("not find shareTablePO by id: " + id);
        }
    }

    /**
     * 查询分片匹配结果
     * @param shareTableResultForm
     * @return
     */
    public Result<List<ShareTableDbInfoVO>> info(ShareTableResultForm shareTableResultForm) {
        ShareTablePO shareTablePO;
        if (shareTableResultForm.getShareTableId() != null){
            shareTablePO = shareTableService.getShareById(shareTableResultForm.getShareTableId());
        } else if(StrUtil.isNotBlank(shareTableResultForm.getTemplateTableName())){
            shareTablePO = shareTableService.getShareByTemplateTableName(shareTableResultForm.getTemplateTableName());
        } else {
            return Result.failMessage("parameters must be passed to one of shareTableId or templateTableName");
        }
        if (shareTablePO != null){
            if (shareTableResultForm.getShareTableBegin() != null){
                shareTablePO.setShareTableBegin(shareTableResultForm.getShareTableBegin());
            }
            DateTime date;
            if (shareTableResultForm.getShareTableEnd() != null){
                date = DateUtil.date(shareTableResultForm.getShareTableEnd());
            } else {
                date = DateUtil.date(System.currentTimeMillis());
            }
            if (shareTableResultForm.getShareTableAdvance() != null){
                shareTablePO.setShareTableAdvance(shareTableResultForm.getShareTableAdvance());
            }
            return Result.ok(dbService.getShareDbByShareTableId(shareTablePO.getId())
                    .parallelStream()
                    .map(e -> {
                        DruidDataSource dataSource = getDruidDataSource(e);
                        ShareTableDbInfoVO shareTableDbInfoVO = ShareTableDbInfoVO.builder()
                                .url(e.getUrl())
                                .shareTableInfoVO(showAllTableResult(shareTablePO, getTemplateTableDDL(shareTablePO.getTemplateTableName()), dataSource, date))
                                .build();
                        dataSource.close();
                        return shareTableDbInfoVO;})
                    .collect(Collectors.toList()));
        } else {
            return Result.ok(new ArrayList<>());
        }
    }
}
